﻿---------------------------------------------------------------------------------------------------
CREATE PROCEDURE [ApplicationManagement].[SaveApplicant]
@PersonOID				bigint			= NULL,
@Version				rowversion		= NULL,
@PersonVersion			rowversion		= NULL,
@FirstName				varchar(128)	= NULL,
@LastName				varchar(128)	= NULL,
@MiddleInitial			char(01)		= NULL,
@DOB					smalldatetime	= NULL,
@Sex					char(01)		= NULL,
@Phone1					varchar(20)		= NULL,
@Phone2					varchar(20)		= NULL,
@eMailAddress			varchar(128)	= NULL,
@KinshipOID				bigint			= NULL,
@AddressOID				bigint			= NULL

AS 

---------------------------------------------------------------------------------------------------
--						Liberty
---------------------------------------------------------------------------------------------------
--  Stored Procedure :: SaveApplicant
--                                                                            
---------------------------------------------------------------------------------------------------

SET NOCOUNT ON 

DECLARE @newOID bigint

CREATE TABLE #PersonCols
(oid		bigint
,version	rowversion
)


---------------------------------------------------------------------------------------------------
-- Step 1: Call SavePerson, since an Applicant is really a person.
---------------------------------------------------------------------------------------------------
INSERT INTO #PersonCols (oid, version)
EXECUTE		SavePerson	@oid			= @PersonOID,
						@version		= @PersonVersion,
						@FirstName		= @FirstName,
						@LastName		= @LastName,
						@MiddleInitial	= @MiddleInitial,
						@DOB			= @DOB,
						@Sex			= @Sex,
						@Phone1			= @Phone1,
						@Phone2			= @Phone2,
						@eMailAddress	= @eMailAddress
IF @@error <> 0
BEGIN
	RAISERROR(99999, 16, 1, 'ServiceMan' )
END


---------------------------------------------------------------------------------------------------
-- Step 2: Ensure the addresses exist
---------------------------------------------------------------------------------------------------

IF NOT EXISTS(SELECT 1 FROM dbo.Addresses WHERE OID = @AddressOID)
BEGIN
	RAISERROR('Address does not exist', 16, 1)
	GOTO ExitError
END


---------------------------------------------------------------------------------------------------
-- Step 3: "Save" the Applicant
---------------------------------------------------------------------------------------------------
-- If no Appliant OID was passed in, this is a new Person and ServiceMan; get the Person's
-- OID returned from the SavePerson call and perform an insert.
IF ISNULL(@PersonOID, 0) <= 0
BEGIN
	SELECT @PersonOID = oid, @PersonVersion = Version
	FROM #PersonCols

	INSERT INTO Applicant
	(OID, KinshipID, AddressID, UpdatedDate, CreatedDate)
	VALUES
	(@PersonOID, @KinshipOID, @AddressOID, GETDATE(), GETDATE())

	-- 1.3. Retutn OID and timestamp of newly inserted object
	SELECT @Version = Version 
	FROM ServiceMen 
	WHERE OID = @PersonOID
END

-- If Applicant OID was passed in, perform an update
ELSE
BEGIN
	-- Attempt updating object (using both OID and timestamp for concurrency reasons)
	UPDATE Applicant
	SET KinshipID = @KinshipOID
		,AddressID = @AddressOID
		,UpdatedDate = GETDATE()
	WHERE (OID = @PersonOID) 
	AND (Version = @Version)

	-- If no records were found (and updated) - it means that another process had updated 
	-- this record (therefore timestamp will be different) or record was deleted (OID not found)
	-- Either way, we need to raise exception
	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR(99999, 16, 1, 'Applicant')
	END

	-- Record was found - return OID and new timestamp
	ELSE
	BEGIN 
		SELECT @Version = Version 
		FROM Applicant 
		WHERE OID = @PersonOID
	END
END


---------------------------------------------------------------------------------------------------
-- Step 5: Return all the associated values to the caller.
---------------------------------------------------------------------------------------------------

SELECT @PersonOID as 'OID', 
		@Version as 'Version', 
		@PersonVersion as 'PersonVersion'


RETURN(0)


ExitError:
	RETURN(1)


